package com.erps.db;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import com.erps.CommonUtils.G_KeyValue;
import com.erps.CommonUtils.StringEx;

public class DBManager {
	private static final Logger log = LoggerFactory.getLogger(DBManager.class);

	public static String ServerDateTime() {
		String cDataKey = "";
		try {
			cDataKey = MDBConfig.readConfig(DBType.MASTER).getDateKey();
		} catch (Exception e) {
			cDataKey = "";
		}
		if (cDataKey.length() > 0)
			return GetStrValue(cDataKey);
		else
			return StringEx.getDateStr(new Date(), "yyyy-MM-dd HH:mm:ss");
	}

	public static List<Map<String, String>> QueryData(String strSQL) {

		Connection dbConnect = DbConnectionPool.Instance().getConnection(DBType.SLAVE);
		return QueryData(dbConnect, strSQL);
	}

	private static List<Map<String, String>> QueryData(Connection dbConnect, String strSQL) {
		if ((strSQL == null) || (strSQL.trim().equals("")))
			return (null);
		ArrayList<Map<String, String>> rows = new ArrayList<Map<String, String>>();
		try {
			Statement sment = dbConnect.createStatement();
			ResultSet rs = sment.executeQuery(strSQL);
			ResultSetMetaData rsmd = rs.getMetaData();
			rs.getRow();
			int FieldCount = rsmd.getColumnCount();
			while (rs.next()) {
				HashMap<String, String> row = new HashMap<String, String>();
				for (int i = 1; i <= FieldCount; i++) {
					String cFieldName = rsmd.getColumnLabel(i).toLowerCase();
					String objValue = rs.getString(i);
					if (objValue == null)
						row.put(cFieldName, "");
					else {
						row.put(cFieldName, String.valueOf(objValue));
					}
				}
				rows.add(row);
			}
			rsmd = null;
			sment.close();
			rs.close();
			sment = null;
			rs = null;
			return (rows);
		} catch (SQLException ex) {
			log.error("SQL:" + strSQL);
			log.error(ex.getMessage());
			return (rows);
		} finally {
			if (dbConnect != null)
				try {
					dbConnect.close();
					dbConnect = null;
				} catch (SQLException e) {
				}
		}
	}

	public static int ExecSQL(String[] sqlsList, List<Object[]> ParmsList) {
		Connection dbConnect = DbConnectionPool.Instance().getConnection(DBType.MASTER);
		List<String> v_sqlsList = Arrays.asList(sqlsList);
		return ExecSQL(dbConnect, v_sqlsList, ParmsList);
	}

	public static int ExecSQL(List<String> sqlsList, List<Object[]> ParmsList) {
		Connection dbConnect = DbConnectionPool.Instance().getConnection(DBType.MASTER);
		return ExecSQL(dbConnect, sqlsList, ParmsList);
	}

	private static int ExecSQL(Connection dbConnect, List<String> sqlsList, List<Object[]> ParmsList) {
		int iEffect = 0;
		String sql = "";
		Object[] vps = null;
		try {
			dbConnect.setAutoCommit(false);
			for (int i = 0; i < sqlsList.size(); i++) {
				sql = sqlsList.get(i);
				if ((sql != null) && (sql.length() > 10)) {
					vps = (ParmsList == null) ? null : ParmsList.get(i);
					if (vps == null) {
						Statement sment = dbConnect.createStatement();
						iEffect = iEffect + sment.executeUpdate(sql);
					} else {
						PreparedStatement sment = dbConnect.prepareStatement(sql);
						for (int k = 0; k < vps.length; k++) {
							Object objValue = vps[k];
							sment.setObject(k + 1, objValue);
						}
						iEffect = iEffect + sment.executeUpdate();
					}
				}
			}
			dbConnect.commit();
			dbConnect.setAutoCommit(true);
		} catch (Exception ex) {
			log.error(ex.getMessage());
			log.error("SQL:" + sql + "\n\r" + ex.getMessage());
			int iLength = (vps == null) ? 0 : vps.length;
			for (int i = 0; i < iLength; i++) {
				log.error("SQL:" + vps[i].toString());
			}
			try {
				dbConnect.rollback();
			} catch (Exception e) {
				log.error(e.getMessage());
			}
		} finally {
			if (dbConnect != null)
				try {
					dbConnect.close();
					dbConnect = null;
				} catch (SQLException e) {
				}
		}
		return (iEffect);
	}

	public static int ExecSQL(String strSQL, Object[] Parms) {
		List<String> sqlList = new ArrayList<String>(1);
		sqlList.add(strSQL);
		List<Object[]> ParmList = new ArrayList<Object[]>(1);
		ParmList.add(Parms);
		return ExecSQL(sqlList, ParmList);
	}

	public static List<G_KeyValue> QueryList(String strSQL) {
		List<Map<String, String>> dtRows = QueryData(strSQL);
		List<G_KeyValue> dtRet = new ArrayList<G_KeyValue>();
		int iLength = dtRows.size();
		for (int i = 0; i < iLength; i++) {
			Map<String, String> arKey = (Map<String, String>) dtRows.get(i);
			String cKeyName = arKey.get("keyname");
			String cKeyValue = arKey.get("keyid");
			G_KeyValue arValue = new G_KeyValue(cKeyName, cKeyValue);
			dtRet.add(arValue);
		}
		return dtRet;
	}

	// ͨ��SQL
	public static Map<String, String> GetRow(String strSQL) {
		List<Map<String, String>> dtRows = QueryData(strSQL);
		if ((dtRows != null) && (dtRows.size() > 0))
			return (Map<String, String>) dtRows.get(0);
		else
			return null;
	}

	public static int ExecSQL(List<String> sqlList) {
		String[] sqls = new String[sqlList.size()];
		int iLength = (sqlList == null) ? 0 : sqlList.size();
		for (int i = 0; i < iLength; i++) {
			sqls[i] = sqlList.get(i);
		}
		return ExecSQL(sqls);
	}

	public static List<Map<String, String>> QueryData(String vSQL, String vOrderBy, int iPageNO, int iPageSize) {
		StringBuilder sql = new StringBuilder();
		int iStart = (iPageNO - 1) * iPageSize;
		if (iStart < 0)
			iStart = 0;
		// MYSQL
		String cKeyID = DBType.SLAVE;
		if (DBType.getDBType(cKeyID) == DBType.MYSQL) {
			sql.append(vSQL);
			if (vOrderBy.length() > 0)
				sql.append(" " + vOrderBy);
			sql.append(" limit " + Integer.toString(iPageSize) + " offset " + String.valueOf(iStart));
		}
		// POSTGRE
		if (DBType.getDBType(cKeyID) == DBType.POSTGRE) {
			sql.append(vSQL);
			if (vOrderBy.length() > 0)
				sql.append(" " + vOrderBy);
			sql.append(" offset " + String.valueOf(iStart) + " limit " + Integer.toString(iPageSize));
		}

		// ORACLE
		if (DBType.getDBType(cKeyID) == DBType.ORACLE) {

		}

		// MSSQL2005
		if (DBType.getDBType(cKeyID) == DBType.MSSQL) {
			sql.append(" select * from (");
			sql.append("   select *, row_number() over(" + vOrderBy + ") as recno");
			sql.append("   from (" + vSQL + ") as t_abc");
			sql.append("   ) as t_xyz");
			sql.append(" where recno between " + iStart + " and " + iPageSize);
		}
		return QueryData(sql.toString());
	}

	public static int GetMaxID(String cTableName, String cFieldName, String cWhereParm) {
		StringBuffer sql = new StringBuffer();
		sql.append("select max(" + cFieldName + "+1) ");
		sql.append(" from " + cTableName);
		if ((cWhereParm != null) && (cWhereParm.length() > 0))
			sql.append(" where " + cWhereParm);
		String cStr = GetStrValue(sql.toString());
		return StringEx.getInt(cStr, 1);
	}

	public static String GetStrValue(String sql) {
		Connection dbConnect = DbConnectionPool.Instance().getConnection(DBType.SLAVE);
		return GetStrValue(dbConnect, sql);
	}

	private static String GetStrValue(Connection dbConnect, String sql) {
		String sCode = "";
		try {
			Statement sment = dbConnect.createStatement();
			ResultSet rs = sment.executeQuery(sql);
			if (rs.next()) {
				if (rs.getObject(1) != null)
					sCode = rs.getString(1);
			} else
				sCode = null;
			rs.close();
			sment.close();
		} catch (Exception ex) {
			log.error("SQL:" + sql);
			log.error(ex.getMessage());
		} finally {
			if (dbConnect != null) {
				try {
					dbConnect.close();
					dbConnect = null;
				} catch (SQLException e) {
				}
			}
		}
		return sCode;
	}

	// / 隐藏
	private static int ExecSQL(Connection dbConnect, String[] sqls) {
		String sql = "";
		int iEffect = 0;
		try {
			Statement sment = dbConnect.createStatement();
			dbConnect.setAutoCommit(false);
			for (int i = 0; i < sqls.length; i++) {
				sql = sqls[i];
				if ((sql != null) && (sql.length() > 10)) {
					iEffect = iEffect + sment.executeUpdate(sql);
				}
			}
			dbConnect.commit();
			dbConnect.setAutoCommit(true);
			sment.close();
		} catch (SQLException ex) {
			log.error("SQL:" + ex.getMessage());
			log.error("\n\r" + sql);
			try {
				dbConnect.rollback();
				iEffect = -1;
				log.error("SQL:" + ex.getMessage());
				log.error("\n\r" + sql);
			} catch (SQLException e) {
				e.printStackTrace();
			}
		} finally {
			if (dbConnect != null) {
				try {
					dbConnect.close();
					dbConnect = null;
				} catch (SQLException e) {
				}
			}
		}
		return (iEffect);
	}

	public static int ExecSQL(String strSQL) {
		String[] sqls = new String[1];
		sqls[0] = strSQL;
		return ExecSQL(sqls);
	}

	public static int ExecSQL(String[] sqls) {
		Connection dbConnect = DbConnectionPool.Instance().getConnection(DBType.MASTER);
		return ExecSQL(dbConnect, sqls);
	}

}